# -*- codeing=utf-8 -*-
# @Time : 2021/1/16 14:24
# @Author : 李思行
# @File : sendEmail.py
# @Software : PyCharm

from selenium import webdriver
import mysql.connector
import xlwt
wb = webdriver.Chrome()
wb.implicitly_wait(3)

def main():
    openWeb()
    datalist=getData()
    saveData(datalist)
    wb.quit()
    #保存到数据库中
    mydb=mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="lsx13575561025",
        database="yiqing_database"
    )
    dbpath=mydb.cursor()
    savaDataSql(datalist,dbpath)
    mydb.commit()

#打开百度搜索疫情,最后进入疫情实时大数据报告页面
def openWeb():
    wb.get('https://www.baidu.com/')
    try:
        key_words=wb.find_elements_by_xpath('//input[@id="kw"]')
        enter_1=wb.find_elements_by_xpath('//input[@id="su"]')
    except:
        print("ERROR:获取页面元素失败")
    key_words[0].send_keys("疫情")
    enter_1[0].click()
    print("咱已完成搜索关键词“疫情”")
    #切换页面
    for handle in   wb.window_handles:
        wb.switch_to.window(handle)
        if  '疫情'    in  wb.title:
            break
    try:
        enter_2= wb.find_elements_by_xpath('//div[@class="tips-right"]')
    except:
        print("ERROR:获取页面元素失败")
    enter_2[0].click()
    for handle in   wb.window_handles:
        wb.switch_to.window(handle)
        if  '实时更新'    in  wb.title:
            break
    print("现在咱进入了大数据报告页面")
#爬取大数据报告页面的数据
def getData():
    more=wb.find_elements_by_xpath('//div[@id="nationTable"]/div/span')
    # 点开更多
    more[0].click()
    units=wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr')
    x = 0
    for unit in units:
        wb.execute_script("arguments[0].scrollIntoView(false)",unit);
        unit.find_elements_by_xpath('//td/div/span[1]')[x].click()
        x += 1
    all_elements=wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr')
    y=0
    z=0
    m=0
    datalist=[]
    for son in all_elements:
        data=[]
        if  y==1:
            a=m+z+1
            nons=wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr[%d]/td/table[1]/tbody/tr'%a)
            print(len(nons))
            w = 0
            for grandson in nons:
                grandson_data=[]
                grandson_address=wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr[%d]/td/table[1]/tbody/tr/td//div[1]'%a)
                print(grandson_address[w].text, end="\t")
                grandson_data.append(grandson_address[w].text)
                grandson_addNumber =  wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr[%d]/td/table[1]/tbody/tr/td[2]'%a)
                grandson_data.append( grandson_addNumber[w].text)
                grandson_nowNumber = wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr[%d]/td/table[1]/tbody/tr/td[3]'%a)
                grandson_data.append(grandson_nowNumber[w].text)
                grandson_allNumber = wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr[%d]/td/table[1]/tbody/tr/td[4]'%a)
                grandson_data.append(grandson_allNumber[w].text)
                grandson_crueNumber = wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr[%d]/td/table[1]/tbody/tr/td[5]'%a)
                grandson_data.append(grandson_crueNumber[w].text)
                grandson_deadNumber = wb.find_elements_by_xpath('//div[@id="nationTable"]/table/tbody/tr[%d]/td/table[1]/tbody/tr/td[6]'%a)
                grandson_data.append(grandson_deadNumber[w].text)
                datalist.append(grandson_data)
                print("完成")
                w+=1
            m+=1
            y=0
        else:
            son_address = son.find_elements_by_xpath('//td/div/span[2]')
            print(son_address[z].text, end="\t")
            data.append(son_address[z].text)
            son_addNumber = son.find_elements_by_xpath('//td[2]')
            data.append(son_addNumber[z].text)
            son_nowNumber = son.find_elements_by_xpath('//td[3]')
            data.append(son_nowNumber[z].text)
            son_allNumber = son.find_elements_by_xpath('//td[4]')
            data.append(son_allNumber[z].text)
            son_crueNumber = son.find_elements_by_xpath('//td[5]')
            data.append(son_crueNumber[z].text)
            son_deadNumber = son.find_elements_by_xpath('//td[6]')
            data.append(son_deadNumber[z].text)
            datalist.append(data)
            print("完成")
            if str(son_address[z].text) == "香港" or str(son_address[z].text) == "澳门" or str(son_address[z].text) == "台湾":
                print("属于特别行政区")
            else:
                print("不属于特别行政区")
                y = 1
            z += 1


    return  datalist
#保存数据
def saveData(datalist):
    workbook=xlwt.Workbook(encoding="UTF-8")
    worksheet=workbook.add_sheet('疫情大数据报.xls')
    col=("疫情地区","新增","现有","累计","治愈","死亡")
    for i   in range(0,6):
        worksheet.write(0,i,col[i])
    for i   in range(0,len(datalist)):
        print("第%d条"%i)
        data=datalist[i]
        for j   in range(0,6):
            worksheet.write(i+1,j,data[j])
    workbook.save('疫情大数据报.xls')
#保存数据到数据库
def savaDataSql(datalist,dbpath):

    cur=dbpath
    for data in datalist:
        for index in range(len(data)):
            if data[index]=='-':
                data[index]='0'
        sql="INSERT INTO yiqing (address,adds,now,totall,cure,dead) values (%s,%s,%s,%s,%s,%s)"
        var=(data[0],data[1],data[2],data[3],data[4],data[5])
        cur.execute(sql,var)

# #创建一个数据库
# def createDb(dbpath):
#     conn=sqlite3.connect(dbpath)
#     cursor=conn.cursor()
#     sql='''
#         create  table   yiqing
#         (
#             id integer primary  key autoincrement,
#             address varchar ,
#             adds int,
#             now int,
#             totall int,
#             cure int,
#             dead int
#         )
#     '''
#     cursor.execute(sql)
#     conn.commit()
#     conn.close()
if __name__ == '__main__':
    main()